"""Restructure product_attributes and product_specifications tables

Revision ID: 20241005_restructure_tables
Revises: 20241005_add_foreign_keys
Create Date: 2025-10-05 18:39:00.000000

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import mysql

# revision identifiers, used by Alembic.
revision: str = '20241005_restructure_tables'
down_revision: Union[str, Sequence[str], None] = '20241005_add_indexes'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema - Restructure tables for new model"""
    # ### commands auto generated by Alembic - please adjust! ###

    # 1. Add columns to attribute_values table for foreign key
    op.add_column('attribute_values', sa.Column('attribute_id_fk', sa.Integer(), nullable=True, comment='属性定义ID外键'))

    # Copy data from attribute_id to attribute_id_fk if exists
    op.execute("UPDATE attribute_values SET attribute_id_fk = attribute_id WHERE attribute_id IS NOT NULL")

    # Drop the old column and rename the new one
    op.drop_column('attribute_values', 'attribute_id')
    op.alter_column('attribute_values', 'attribute_id_fk', new_column_name='attribute_id')

    # 2. Drop and recreate product_attributes table with new structure
    op.drop_table('product_attributes')

    op.create_table('product_attributes',
        sa.Column('id', sa.Integer(), nullable=False, comment='商品属性ID'),
        sa.Column('product_id', sa.Integer(), nullable=False, comment='商品ID'),
        sa.Column('attribute_id', sa.Integer(), nullable=False, comment='属性定义ID'),
        sa.Column('attribute_value_id', sa.Integer(), nullable=False, comment='属性值ID'),
        sa.Column('stock_quantity', sa.Integer(), nullable=False, default=0, comment='该属性组合的库存'),
        sa.Column('is_default', sa.Boolean(), nullable=False, default=False, comment='是否默认选项'),
        sa.Column('sort_order', sa.Integer(), nullable=False, default=0, comment='排序顺序'),
        sa.Column('is_active', sa.Boolean(), nullable=False, default=True, comment='是否激活'),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True, comment='创建时间'),
        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True, comment='更新时间'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index('ix_product_attributes_id', 'product_attributes', ['id'], unique=False)

    # 3. Add columns to specification_definitions table for foreign key
    op.add_column('specification_definitions', sa.Column('group_id_fk', sa.Integer(), nullable=True, comment='规格分组ID外键'))

    # Copy data from group_id to group_id_fk if exists
    op.execute("UPDATE specification_definitions SET group_id_fk = group_id WHERE group_id IS NOT NULL")

    # Drop the old column and rename the new one
    op.drop_column('specification_definitions', 'group_id')
    op.alter_column('specification_definitions', 'group_id_fk', new_column_name='group_id')

    # 4. Drop and recreate product_specifications table with new structure
    op.drop_table('product_specifications')

    op.create_table('product_specifications',
        sa.Column('id', sa.Integer(), nullable=False, comment='规格ID'),
        sa.Column('product_id', sa.Integer(), nullable=False, comment='商品ID'),
        sa.Column('specification_id', sa.Integer(), nullable=False, comment='规格定义ID'),
        sa.Column('value', sa.String(length=500), nullable=False, comment='规格值'),
        sa.Column('sort_order', sa.Integer(), nullable=False, default=0, comment='排序顺序'),
        sa.Column('is_active', sa.Boolean(), nullable=False, default=True, comment='是否激活'),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True, comment='创建时间'),
        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True, comment='更新时间'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index('ix_product_specifications_id', 'product_specifications', ['id'], unique=False)

    # 5. Add foreign key constraints
    # Attribute foreign keys
    op.create_foreign_key(
        'fk_attribute_values_attribute_id',
        'attribute_values',
        'attribute_definitions',
        ['attribute_id'],
        ['id'],
        ondelete='CASCADE'
    )

    op.create_foreign_key(
        'fk_product_attributes_product_id',
        'product_attributes',
        'products',
        ['product_id'],
        ['id'],
        ondelete='CASCADE'
    )

    op.create_foreign_key(
        'fk_product_attributes_attribute_id',
        'product_attributes',
        'attribute_definitions',
        ['attribute_id'],
        ['id'],
        ondelete='CASCADE'
    )

    op.create_foreign_key(
        'fk_product_attributes_attribute_value_id',
        'product_attributes',
        'attribute_values',
        ['attribute_value_id'],
        ['id'],
        ondelete='CASCADE'
    )

    # Specification foreign keys
    op.create_foreign_key(
        'fk_specification_definitions_group_id',
        'specification_definitions',
        'specification_groups',
        ['group_id'],
        ['id'],
        ondelete='SET NULL'
    )

    op.create_foreign_key(
        'fk_product_specifications_product_id',
        'product_specifications',
        'products',
        ['product_id'],
        ['id'],
        ondelete='CASCADE'
    )

    op.create_foreign_key(
        'fk_product_specifications_specification_id',
        'product_specifications',
        'specification_definitions',
        ['specification_id'],
        ['id'],
        ondelete='CASCADE'
    )

    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema"""
    # ### commands auto generated by Alembic - please adjust! ###

    # Drop foreign keys
    op.drop_constraint('fk_product_specifications_specification_id', 'product_specifications', type_='foreignkey')
    op.drop_constraint('fk_product_specifications_product_id', 'product_specifications', type_='foreignkey')
    op.drop_constraint('fk_specification_definitions_group_id', 'specification_definitions', type_='foreignkey')
    op.drop_constraint('fk_product_attributes_attribute_value_id', 'product_attributes', type_='foreignkey')
    op.drop_constraint('fk_product_attributes_attribute_id', 'product_attributes', type_='foreignkey')
    op.drop_constraint('fk_product_attributes_product_id', 'product_attributes', type_='foreignkey')
    op.drop_constraint('fk_attribute_values_attribute_id', 'attribute_values', type_='foreignkey')

    # Drop recreated tables
    op.drop_index('ix_product_specifications_id', table_name='product_specifications')
    op.drop_table('product_specifications')
    op.drop_index('ix_product_attributes_id', table_name='product_attributes')
    op.drop_table('product_attributes')

    # Revert specification_definitions.group_id
    op.add_column('specification_definitions', sa.Column('group_id_fk', sa.Integer(), nullable=True, comment='规格分组ID'))
    op.execute("UPDATE specification_definitions SET group_id_fk = group_id WHERE group_id IS NOT NULL")
    op.drop_column('specification_definitions', 'group_id')
    op.alter_column('specification_definitions', 'group_id_fk', new_column_name='group_id')

    # Revert attribute_values.attribute_id
    op.add_column('attribute_values', sa.Column('attribute_id_fk', sa.Integer(), nullable=False, comment='属性定义ID'))
    op.execute("UPDATE attribute_values SET attribute_id_fk = attribute_id WHERE attribute_id IS NOT NULL")
    op.drop_column('attribute_values', 'attribute_id')
    op.alter_column('attribute_values', 'attribute_id_fk', new_column_name='attribute_id')

    # ### end Alembic commands ###